#!/usr/bin/env python  
#coding=utf-8

import os
import sqlite3

#定义pearson相关计算函数
def pearson(x, y):
  n = len(x)
  vals = range(n)
  #求和
  sumx = sum([float(x[i]) for i in vals])
  sumy = sum([float(y[i]) for i in vals])
  #求平方和
  sumxSq = sum([x[i]**2.0 for i in vals])
  sumySq = sum([y[i]**2.0 for i in vals])
  #求乘积之和
  pSum = sum([x[i]*y[i] for i in vals])
  #计算pearson相关系数
  num = pSum - (sumx*sumy/n)
  den = ((sumxSq - pow(sumx, 2)/n)*(sumySq - pow(sumy, 2)/n))**.5
  if den==0: return 1
  r = num/den
  return r

##使字典的所有的value都为0
def setDicValuesZero(dic):
  for dickey in dic.keys():
      dic.update({dickey:0})
  return dic

##使字典的所有的value都为0
#def setDicValuesZero(dic):
#  dic = dic.fromkeys(dic, 0)
#  return dic
  

#Begin!!

#设定脚本所在目录为工作目录
os.chdir(os.path.dirname(__file__))


#数据库连接
conn = sqlite3.connect('doubanMoviesDB.db')
curs = conn.cursor()

#抽取分词信息
sourceQuery = 'SELECT "subjectid", "segment" FROM "movies"'
curs.execute(sourceQuery)

#获取SQL查询结果
sourceQueryResults = curs.fetchall()
resultsLen = len(sourceQueryResults)

n = 58

#双循环
for i in range(resultsLen)[n:]:
  print i
  for j in range(resultsLen)[i+1:]:
    
    sourceSubjectIdX = sourceQueryResults[i][0]
    sourceSegmentX = sourceQueryResults[i][1]
    segDicX = eval(sourceSegmentX)
    
    sourceSubjectIdY = sourceQueryResults[j][0]
    sourceSegmentY = sourceQueryResults[j][1]
    segDicY = eval(sourceSegmentY)
    
    segDicTemp = segDicX.copy()
    segDicTemp.update(segDicY)
    segDicTempX = segDicTemp.copy()
    segDicTempY = segDicTemp.copy()
    segDicTempX = setDicValuesZero(segDicTempX)
    segDicTempY = setDicValuesZero(segDicTempY)
    
    for segKey in segDicX.keys():
      #segDicTempX.update({segKey:segDicX[segKey]})
      segDicTempX.update({segKey:1})
      
    for segKey in segDicY.keys():
      #segDicTempY.update({segKey:segDicY[segKey]})
      segDicTempY.update({segKey:1})
      
    vetorX = segDicTempX.values()
    vetorY = segDicTempY.values()
        
    correlation = pearson(vetorX, vetorY)
    similarity = (correlation + 1)/2
    
    insertQuery = 'insert into similarity values('+str(sourceSubjectIdX)+','+str(sourceSubjectIdY)+','+str(similarity)+')'
    
    print insertQuery
    
#    curs.execute(insertQuery)
#    conn.commit()
    
#关闭数据库连接
conn.close()


  
